SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_CDS_DistributorBillingConfigList]
	@SortExpression varchar(50),
	@MaximumRows int = null, 
	@StartRowIndex int = null
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLString NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)

	/* Build the SQL string once.*/
	SET @SQLString = N'WITH ItemRN AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + ') AS RowNum, DBC.[ID], DBC.[DistributorID], DC.[name] AS [DistributorName], DBC.[BillingConfigID], BC.[Name] AS [BillingConfigName], [BillingStartDate], [TrialAccount], [TrialAccountDate], [ContractDate], [ServiceStatement], [AcumenID], [CurrencyID], [ExchangeRate], [Enabled] FROM [DnnProduct_CDS_DistributorBillingConfig] DBC'
	SET @SQLString = @SQLString + N' INNER JOIN [DISC_DistributorConfig] DC ON DBC.DistributorID = DC.distID	LEFT JOIN [DnnProduct_CDS_BillingConfig] BC ON DBC.BillingConfigID = BC.ID'

	SET @SQLString = @SQLString + N' ) SELECT [ID], [DistributorID], [DistributorName], [BillingConfigID], [BillingConfigName], [BillingStartDate], [TrialAccount], [TrialAccountDate], [ContractDate], [ServiceStatement], [AcumenID], [CurrencyID], [ExchangeRate], [Enabled] FROM ItemRN WHERE RowNum BETWEEN @startRowIndex+1 AND (@startRowIndex+@maximumRows);'
	SET @ParmDefinition = N'@MaximumRows int, @StartRowIndex int'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @MaximumRows, @StartRowIndex
END
GO
